package com.connect.report.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.connect.report.entity.ReportEntity;

public class ReportEntityDaoImp implements ReportEntityDao {
	private HibernateTemplate hibernateTemplate;
	public void setSessionFactory(SessionFactory sessionFactory){
		this.hibernateTemplate = new HibernateTemplate(sessionFactory);
	}
	@Override
	public List<ReportEntity> getReportIssuerEntities(String date, String issId, String acqId) {
		IssuerCallback action = new IssuerCallback(date, issId, acqId);
		return (List<ReportEntity>)hibernateTemplate.execute(action);
	}
	@Override
	public List<ReportEntity> getReportAcquirerEntities(String date, String acqId, String issId) {
		AcquirerCallback action = new AcquirerCallback(date,acqId, issId);
		return ( List<ReportEntity> )hibernateTemplate.execute(action);
	}
	
	// private class
	private class AcquirerCallback implements HibernateCallback<List<ReportEntity>>{
		private String date;
		private String acqId;
		private String issId;
		public AcquirerCallback(String date, String acqId, String issId){
			this.date = date;
			this.acqId = acqId;
			this.issId = issId;
		}
		@Override
		public List<ReportEntity> doInHibernate(Session session) throws HibernateException,
				SQLException {
			AcquirerWork work = new AcquirerWork(date,acqId, issId);
			session.doWork(work);
			return work.getReportEntities();
		}
		
	}
	private class AcquirerWork implements Work{
		private String date;
		private String acqId;
		private String issId;
		private List<ReportEntity> entities;
		public AcquirerWork(String date, String acqId, String issId){
			this.date = date;
			this.acqId = acqId;
			this.issId = issId;
		}
		@Override
		public void execute(Connection conn) throws SQLException {
			String sql = "SELECT distinct AUT.RRNO RRNO,AUT.CARD_NO CARD, AUT.TERMID TERMINAL, "
					+"to_char(cat.maker_dttm,'YYYYMMDDHH24MISS')  DATETIME, "
					+"aut.proccode PROCCODE, aut.response_code RES," 
					+"aut.txn_ccy CURR,to_char(aut.txnamt*100,'000000009999999') AMT, " 
					+"to_char(aut.charge*100,'000999999') FEE " 
					+"FROM AUTHCTL AUT, CARD_ACCT_TXN CAT WHERE "
					+"AUT.CARD_NO = CAT.CARD_NO AND AUT.RRNO = CAT.RRNO "
					+"AND TO_CHAR(AUT.TXNREQDTTIME,'YYYY-MM-DD') = ? AND cat.acq_id =? AND cat.issr_id=?";
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1,date);
			stat.setString(2, acqId);
			stat.setString(3, issId);
			ResultSet res = stat.executeQuery();
			entities = new ArrayList<ReportEntity>();
			while(res.next()){
				String pan 		= res.getString("CARD");
				String termId 	= res.getString("TERMINAL");
				String dateTime = res.getString("DATETIME");
				String type 	= res.getString("PROCCODE");
				String flag		= res.getString("RES");
				String amt		= res.getString("AMT").trim();
				String curr		= res.getString("CURR");
				String fee		= res.getString("FEE").trim();
				
				if (flag == null) flag = "--";
				
				for(int j=pan.length();j<19;j++)
					pan = pan + " ";
				for(int j=termId.length();j<16;j++)
					termId = termId + " ";
				ReportEntity reportEntity = new ReportEntity();
				reportEntity.setPan(pan);
				reportEntity.setAtmId(termId);
				reportEntity.setDateTime(dateTime);
				reportEntity.setType(type);
				reportEntity.setFlag(flag);
				reportEntity.setAmount1(amt);
				reportEntity.setCurr(curr);
				reportEntity.setFee(fee);
				entities.add(reportEntity);
			}
		}
		public List<ReportEntity> getReportEntities(){
			return entities;
		}
	}
	
	// issuer
	
	private class IssuerCallback implements HibernateCallback<List<ReportEntity>>{
		private String date;
		private String issId;
		private String acqId;
		public IssuerCallback(String date, String issId, String acqId){
			this.date = date;
			this.issId = issId;
			this.acqId = acqId;
		}
		@Override
		public List<ReportEntity> doInHibernate(Session session) throws HibernateException,
				SQLException {
			IssuerWork work = new IssuerWork(date,issId, acqId);
			session.doWork(work);
			return work.getEntities();
		}
		
	}
	private class IssuerWork implements Work{
		private String date;
		private String issId;
		private String acqId;
		List<ReportEntity> entities;
		public IssuerWork(String date, String issId, String acqId){
			this.date = date;
			this.issId = issId;
			this.acqId = acqId;
		}
		@Override
		public void execute(Connection conn) throws SQLException {
			String sql = "SELECT distinct AUT.RRNO RRNO, AUT.CARD_NO CARD, AUT.TERMID TERMINAL, "
					+"to_char(cat.maker_dttm,'YYYYMMDDHH24MISS')  DATETIME, "
					+"aut.proccode PROCCODE, aut.response_code RES," 
					+"aut.txn_ccy CURR,to_char(aut.txnamt*100,'000000009999999') AMT, " 
					+"to_char(aut.charge*100,'000999999') FEE " 
					+"FROM AUTHCTL AUT, CARD_ACCT_TXN CAT WHERE "
					+"AUT.CARD_NO = CAT.CARD_NO AND AUT.RRNO = CAT.RRNO "
					+"AND TO_CHAR(AUT.TXNREQDTTIME,'YYYY-MM-DD') = ? AND cat.issr_id =? and cat.acq_id =?";
			PreparedStatement stat = conn.prepareStatement(sql);
			stat.setString(1, date);
			stat.setString(2, issId);
			stat.setString(3, acqId);
			ResultSet res = stat.executeQuery();
			entities = new ArrayList<ReportEntity>();
			while(res.next()){
				String pan 		= res.getString("CARD");
				String termId 	= res.getString("TERMINAL");
				String dateTime = res.getString("DATETIME");
				String type 	= res.getString("PROCCODE");
				String flag		= res.getString("RES");
				String amt		= res.getString("AMT").trim();
				String curr		= res.getString("CURR");
				String fee		= res.getString("FEE").trim();
				if (flag == null) flag = "--";
				for(int j=pan.length();j<19;j++)
					pan = pan + " ";
				for(int j=termId.length();j<16;j++)
					termId = termId + " ";
				ReportEntity reportEntity = new ReportEntity();
				reportEntity.setPan(pan);
				reportEntity.setAtmId(termId);
				reportEntity.setDateTime(dateTime);
				reportEntity.setType(type);
				reportEntity.setFlag(flag);
				reportEntity.setAmount1(amt);
				reportEntity.setCurr(curr);
				reportEntity.setFee(fee);
				entities.add(reportEntity);
			}
		}
		
		public List<ReportEntity> getEntities(){
			return entities;
		}
		
	}
}
